package com.ericsson.middleware.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.ericsson.middleware.Startup;
import com.ericsson.middleware.entities.LampState;

@Component
public class LampDao {
	private static DataSource dataSource;
 
	private final static Logger LOGGER = Logger.getLogger(LampDao.class
			.getName());

	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
 
	public void insert(LampState lamp){
 
		String sql = "INSERT INTO lamp_state " +
				"(ID, SERIALID, CURRENTINTENSITY, PWM, TIMESTAMP, LATITUDE, LONGITUDE) VALUES (?, ?, ?, ?, ?, ?, ?)";
		Connection conn = null;
 
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, lamp.getId());
			ps.setInt(2, lamp.getSerialID());
			ps.setInt(3, lamp.getCurrentIntensity());
			ps.setInt(4, lamp.getPWM());
			ps.setString(5, lamp.getTimestamp());	
			ps.setString(6, lamp.getLat());	
			ps.setString(7, lamp.getLong());	
			ps.executeUpdate();
			ps.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
 
	public void update(LampState lamp){
		 
		String sql = "UPDATE lamp_state " +
				" SET SERIALID=?, CURRENTINTENSITY=?, PWM=?, TIMESTAMP=?, LATITUDE=?, LONGITUDE=? " +
				" WHERE ID=?";
		Connection conn = null;
 
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, lamp.getSerialID());
			ps.setInt(2, lamp.getCurrentIntensity());
			ps.setInt(3, lamp.getPWM());
			ps.setString(4, lamp.getTimestamp());
			ps.setString(5, lamp.getLat());
			ps.setString(6, lamp.getLong());
			ps.setString(7, lamp.getId());			
			ps.executeUpdate();
			ps.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
	
	
	public LampState findById(String id){
 
		String sql = "SELECT * FROM lamp_state WHERE ID = ?";
 
		Connection conn = null;
 
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, id);
			LampState lamp = null;
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				lamp = new LampState(
					rs.getString("ID"),
					rs.getInt("SERIALID"), 
					rs.getInt("CURRENTINTENSITY"),
					rs.getInt("PWM"),
					rs.getString("TIMESTAMP"),
					rs.getString("LATITUDE"),
					rs.getString("LONGITUDE")
				);
			}
			rs.close();
			ps.close();
			return lamp;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
				conn.close();
				} catch (SQLException e) {}
			}
		}
	}
	
	public List<LampState> findAll(){
		 
		String sql = "SELECT * FROM lamp_state";
		List<LampState> ll = new LinkedList<LampState>();
		Connection conn = null;
 
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			LampState lamp = null;
			ResultSet rs = ps.executeQuery();
			
			// Fetch each row from the result set
			while (rs.next()) {
				lamp = new LampState(
						rs.getString("ID"),
						rs.getInt("SERIALID"), 
						rs.getInt("CURRENTINTENSITY"),
						rs.getInt("PWM"),
						rs.getString("TIMESTAMP"),
						rs.getString("LATITUDE"),
						rs.getString("LONGITUDE")
					);
			  ll.add(lamp);
			}
			
			rs.close();
			ps.close();
			return ll;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
				conn.close();
				} catch (SQLException e) {}
			}
		}
	}
	
	public void cleanTable(){
		 
		String sql = "DELETE FROM lamp_state;";
		Connection conn = null;
 
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			ps.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
 
}